﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LicMagnaCmsData
{
    public class MnCmsDataSet
    {
        /// <summary>
        /// 麦格纳CMS数据写入正天数据库 Add By Quinn.Ling 201708
        /// </summary>
        /// 

        public static string SetCmsData(string xml,string tablename)
        {

            #region 通用变量定义
            string WHSEID = string.Empty; //厂别
            string AVPART = string.Empty; //成品料号
            string AWPART = string.Empty; //原料料号
            string AVMAJS = string.Empty; //成品项目
            string AWMAJS = string.Empty; //原料项目
            string AVCUSR = string.Empty; //成品客户代码
            string AWCUSR = string.Empty; //原料客户代码
            string KAPO = string.Empty;  // PO
            string KBPO = string.Empty;  // PO
            string KBPT = string.Empty;  //料号
            string KAPLNT = string.Empty; //厂别
            string V6PLNT = string.Empty; //厂别代码
            string KAOSTS = string.Empty; // 是否可用状态
            string KAOVND = string.Empty; //供应商
            string KBISTR = string.Empty; //？？？
            string KJENT = string.Empty; //？？？
            string KJSLIP = string.Empty; //出货单号
            string KJBTC = string.Empty; //TBC ???
            string KKQTYR = string.Empty; //麦格纳收货数量
            string KKITM = string.Empty; //KK键值

            string result = string.Empty;
            List<string> sqllists = new List<string>();
            string strSql = string.Empty;
            DataTable CmsData = App.Xml2DataTable(xml); //将传递的xml转换为Datatable使用
            #endregion

            try
            {
                if (CmsData.Rows.Count > 0)
                {
                    sqllists.Clear();
                    if (tablename == "CMS_STKMM")
                    {
                        #region CMS_STKMM写入
                        strSql = "DELETE FROM MAGNA_CMS_SKU";  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                WHSEID = CmsData.Rows[i]["WHSEID"].ToString();
                                AVPART = CmsData.Rows[i]["AVPART"].ToString(); 
                                AVMAJS = CmsData.Rows[i]["AVMAJS"].ToString();
                                AVCUSR = CmsData.Rows[i]["AVCUSR"].ToString();
                                V6PLNT = CmsData.Rows[i]["V6PLNT"].ToString();

                                strSql = "INSERT INTO MAGNA_CMS_SKU (WHSEID,AVPART,AVMAJS,AVCUSR,V6PLNT,ADD_DATE) VALUES ";
                                strSql += "('" + WHSEID + "','" + AVPART + "','" + AVMAJS + "','" + AVCUSR + "','"+ V6PLNT + "',SYSDATETIME())";
                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "CMS_STKMP")
                    {
                        #region CMS_STKMP写入
                        sqllists.Clear();
                        for (int i = 0; i < CmsData.Rows.Count; i++)
                        {
                            WHSEID = CmsData.Rows[i]["WHSEID"].ToString();
                            AWPART = CmsData.Rows[i]["AWPART"].ToString();
                            AWMAJS = CmsData.Rows[i]["AWMAJS"].ToString();
                            AWCUSR = CmsData.Rows[i]["AWCUSR"].ToString();
                            V6PLNT = CmsData.Rows[i]["V6PLNT"].ToString();

                            strSql = "INSERT INTO MAGNA_CMS_SKU (WHSEID,AVPART,AVMAJS,AVCUSR,V6PLNT,ADD_DATE) VALUES ";
                            strSql += "('" + WHSEID + "','" + AWPART + "','" + AWMAJS + "','" + AWCUSR + "','"+ V6PLNT + "',SYSDATETIME())";
                            sqllists.Add(strSql);
                        }
                        #endregion
                    }
                    else if(tablename == "CMS_POH")
                    {
                        #region CMS_POH写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM "+tablename;  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                KAPO = CmsData.Rows[i]["KAPO#"].ToString();
                                KAPLNT = CmsData.Rows[i]["KAPLNT"].ToString();
                                KAOSTS = CmsData.Rows[i]["KAOSTS"].ToString();
                                KAOVND= CmsData.Rows[i]["KAOVND"].ToString();

                                strSql = "INSERT INTO "+ tablename + "(KAPO#,KAOVND,KAPLNT,KAOSTS,ADD_DATE) VALUES ";
                                strSql += "('" + KAPO + "','"+ KAOVND + "','" + KAPLNT + "','" + KAOSTS + "',SYSDATETIME())";
                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "CMS_POI")
                    {
                        #region CMS_POI写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename;  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                KBPO = CmsData.Rows[i]["KBPO#"].ToString();
                                KBPT = CmsData.Rows[i]["KBPT#"].ToString();
                                KAOSTS = CmsData.Rows[i]["KBISTS"].ToString();
                                KBISTR= CmsData.Rows[i]["KBISTR"].ToString();
                                KKITM= CmsData.Rows[i]["KBITM#"].ToString();

                                strSql = "INSERT INTO " + tablename + "(KBPO#,KBPT#,KBISTS,KBISTR,KBITM#,ADD_DATE) VALUES ";
                                strSql += "('" + KBPO + "','" + KBPT + "','" + KAOSTS + "','"+ KBISTR + "','"+ KKITM + "',SYSDATETIME())";
                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "CMS_PORCBH")
                    {
                        #region CMS_PORCBH写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename;  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                KJENT = CmsData.Rows[i]["KJENT#"].ToString();
                                V6PLNT = CmsData.Rows[i]["KJPLNT"].ToString();
                                KJSLIP = CmsData.Rows[i]["KJSLIP"].ToString();
                                string[] jspslp = KJSLIP.Replace("'", "").Split(' ');
                                if (jspslp.Length > 1)
                                {
                                    KJSLIP = jspslp[0].Trim().ToString();
                                }
                                KBPO = CmsData.Rows[i]["KJPO#"].ToString();
                                KJBTC = CmsData.Rows[i]["KJBTC#"].ToString();

                                strSql = "INSERT INTO " + tablename + "(KJENT#,KJPLNT,KJSLIP,KJPO#,KJBTC#,ADD_DATE) VALUES ";
                                strSql += "('" + KJENT + "','" + V6PLNT + "','" + KJSLIP + "','" + KBPO + "','"+ KJBTC + "',SYSDATETIME())";
                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "CMS_PORCBI")
                    {
                        #region CMS_PORCBI写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename;  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                KJENT = CmsData.Rows[i]["KKENT#"].ToString();
                                KBPO = CmsData.Rows[i]["KKPO#"].ToString();
                                KKQTYR = CmsData.Rows[i]["KKQTYR"].ToString();
                                KKITM = CmsData.Rows[i]["KKITM#"].ToString();

                                strSql = "INSERT INTO " + tablename + "(KKENT#,KKPO#,KKQTYR,KKITM#,ADD_DATE) VALUES ";
                                strSql += "('" + KJENT + "','" + KBPO + "','" + KKQTYR + "','" + KKITM + "',SYSDATETIME())";
                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "V_CMS_PORCH")
                    {
                        #region V_CMS_PORCH写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename + " WHERE JSRDAT =CONVERT(varchar(100), GETDATE(), 23)";  //先清空当天老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            string CommName = string.Empty; //表列名
                            string CommValues = string.Empty; //每列值

                            //构建表列名
                            for (int i = 0; i < CmsData.Columns.Count; i++)
                            {
                                CommName = CommName + CmsData.Columns[i].ToString() + ",";
                            }
                            CommName = CommName.Substring(0, CommName.Length - 1);
                            string[] sArray = CommName.Split(',');

                            //构建数据写入SQL
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                CommValues = "";
                                strSql = @"INSERT INTO " + tablename + "(" + CommName + ")" + " VALUES";
                                foreach (string values in sArray)
                                {
                                    if (values == "ADD_DATE") //对时间数据源做特殊处理
                                    {
                                        CommValues = CommValues + "SYSDATETIME(),";
                                    }
                                    else if (values == "JSPSLP") //出货单号或入库单号 后面可能跟着空格+发票号，需截取
                                    {
                                        string[] jspslp = CmsData.Rows[i][values].ToString().Replace("'", "").Split(' ');
                                        if (jspslp.Length > 1)
                                        {
                                            CommValues = CommValues + "'" + jspslp[0].Trim().ToString() + "',";
                                        }
                                        else
                                        {
                                            CommValues = CommValues + "'" + CmsData.Rows[i][values].ToString().Replace("'", "") + "',";
                                        }
                                    }
                                    else
                                    {
                                        CommValues = CommValues + "'" + CmsData.Rows[i][values].ToString().Replace("'", "") + "',";
                                    }
                                }
                                CommValues = CommValues.Substring(0, CommValues.Length - 1);
                                strSql += "(" + CommValues + ")";

                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else if (tablename == "V_CMS_PORCI")
                    {
                        #region V_CMS_PORCI写入
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename + " WHERE JSRDAT =CONVERT(varchar(100), GETDATE(), 23)";  //先清空当天老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            string CommName = string.Empty; //表列名
                            string CommValues = string.Empty; //每列值

                            //构建表列名
                            for (int i = 0; i < CmsData.Columns.Count; i++)
                            {
                                CommName = CommName + CmsData.Columns[i].ToString() + ",";
                            }
                            CommName = CommName.Substring(0, CommName.Length - 1);
                            string[] sArray = CommName.Split(',');

                            //构建数据写入SQL
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                CommValues = "";
                                strSql = @"INSERT INTO " + tablename + "(" + CommName + ")" + " VALUES";
                                foreach (string values in sArray)
                                {
                                    if (values == "ADD_DATE") //对时间数据源做特殊处理
                                    {
                                        CommValues = CommValues + "SYSDATETIME(),";
                                    }
                                    else if (values == "JSPSLP") //出货单号或入库单号 后面可能跟着空格+发票号，需截取
                                    {
                                        string[] jspslp = CmsData.Rows[i][values].ToString().Replace("'", "").Split(' ');
                                        if (jspslp.Length > 1)
                                        {
                                            CommValues = CommValues + "'" + jspslp[0].Trim().ToString() + "',";
                                        }
                                        else
                                        {
                                            CommValues = CommValues + "'" + CmsData.Rows[i][values].ToString().Replace("'", "") + "',";
                                        }
                                    }
                                    else
                                    {
                                        CommValues = CommValues + "'" + CmsData.Rows[i][values].ToString().Replace("'", "") + "',";
                                    }
                                }
                                CommValues = CommValues.Substring(0, CommValues.Length - 1);
                                strSql += "(" + CommValues + ")";

                                sqllists.Add(strSql);
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        #region 表数据通用写入方法 
                        tablename = "MAGNA_" + tablename;
                        strSql = "DELETE FROM " + tablename;  //先清空老数据
                        sqllists.Add(strSql);
                        if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                        {
                            string CommName = string.Empty; //表列名
                            string CommValues = string.Empty; //每列值

                            //构建表列名
                            for (int i = 0; i < CmsData.Columns.Count; i++)
                            {
                                CommName = CommName + CmsData.Columns[i].ToString() + ",";
                            }
                            CommName = CommName.Substring(0, CommName.Length - 1);
                            string[] sArray = CommName.Split(',');

                            //构建数据写入SQL
                            sqllists.Clear();
                            for (int i = 0; i < CmsData.Rows.Count; i++)
                            {
                                CommValues = "";
                                strSql = @"INSERT INTO "+ tablename + "(" + CommName + ")" + " VALUES";
                                foreach (string values in sArray)
                                {
                                    if(values== "ADD_DATE") //对时间数据源做特殊处理
                                    {
                                        CommValues = CommValues + "SYSDATETIME(),";
                                    }
                                    else
                                    {
                                        CommValues = CommValues + "'" + CmsData.Rows[i][values].ToString().Replace("'", "") + "',";
                                    }
                                }
                                CommValues = CommValues.Substring(0, CommValues.Length - 1);
                                strSql += "(" + CommValues + ")";

                                sqllists.Add(strSql);
                            }
                        }


                        #endregion
                    }

                    //数据提交
                    if (App.ExecuteSQLScriptLists(sqllists, App.DBconWMS))
                    {
                        App.Log("CmsSet获取并写入正天数据库成功", tablename+"记录写入笔数：" + sqllists.Count.ToString());
                    }
                    else
                    {
                        App.Log("CmsSet获取并写入正天数据库失败", tablename + "写入失败笔数：" + sqllists.Count.ToString());
                    }

                }

            }
            catch (Exception ex)
            {
                App.Log("GetCmsData-1.1", ex.Message.ToString());
            }
            return result;
        }
    }
}
